{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fa157b96",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T05:52:30.278919Z",
     "start_time": "2025-06-17T05:52:30.268919Z"
    }
   },
   "source": [
    "# 版本"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73285bf7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T05:54:17.676062Z",
     "start_time": "2025-06-17T05:54:17.658061Z"
    }
   },
   "source": [
    "# 读取数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1298712e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T05:55:35.626520Z",
     "start_time": "2025-06-17T05:55:35.615520Z"
    }
   },
   "source": [
    "## 读取txt文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "68c7488c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:21:09.958004Z",
     "start_time": "2025-06-17T09:21:09.932002Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'29'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('./数据/dim_items（new).txt','r',encoding='utf-8') as f:\n",
    "    data = f.readline()\n",
    "    dim_list = data.split(' ')\n",
    "    \n",
    "dim_list[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "acc5e2b4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:56:08.382027Z",
     "start_time": "2025-06-17T09:56:02.804708Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['3967']"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_file_url = './数据/dim_items（new).txt'\n",
    "test_file_url = './数据/test_items（new).txt'\n",
    "with open(dim_file_url,'r',encoding='utf-8') as f:\n",
    "    # 读取所有数据，并存储到列表中\n",
    "#     data_list = []\n",
    "#     for line in f:\n",
    "#         data_list.append(line.split())\n",
    "#         data_list.append(data.split(' '))\n",
    "    dim_list = [line.split() for line in f]\n",
    "    \n",
    "with open(test_file_url,'r',encoding='utf-8') as f:\n",
    "    test_list = [line.split() for line in f]\n",
    "len(dim_list)\n",
    "test_list[2]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5aeb2096",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:18:43.402621Z",
     "start_time": "2025-06-17T09:18:43.380620Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['3269977',\n",
       " '70',\n",
       " '48909,53517,116593,123580,195786,27275,51565,149870,191566,102692,98704,212072,26989,120826,15463']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_list[499982]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9361d68f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:56:14.163357Z",
     "start_time": "2025-06-17T09:56:08.394027Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['\"29\"',\n",
       " '\"155\"',\n",
       " '\"123950,53517,106068,59598,7503,171811,25618,147905,203432,123580,178091,154365,127004,31897,82406\"']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for data in dim_list:\n",
    "    for index in range(len(data)):\n",
    "        if len(data) == 2:\n",
    "            data.append(\" \") \n",
    "        data[index] = '\"' + data[index] + '\"' \n",
    "dim_list[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "a29460ae",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:55:02.626266Z",
     "start_time": "2025-06-17T09:55:02.598264Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\"123950,53517,106068,59598,7503,171811,25618,147905,203432,123580,178091,154365,127004,31897,82406\"'"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_list[0][2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5cb15c9e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:55:19.719243Z",
     "start_time": "2025-06-17T09:55:19.693242Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('\"29\"',\n",
       " '\"155\"',\n",
       " '\"123950,53517,106068,59598,7503,171811,25618,147905,203432,123580,178091,154365,127004,31897,82406\"')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_list[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "88ecc2b9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:53:52.608261Z",
     "start_time": "2025-06-17T09:53:52.586260Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1417']"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_list[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59d70806",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T05:54:34.027997Z",
     "start_time": "2025-06-17T05:54:34.013996Z"
    }
   },
   "source": [
    "# 保存数据到数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "a438ddc1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:58:55.562589Z",
     "start_time": "2025-06-17T09:58:55.524587Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "表创建成功\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "\n",
    "# 初始化数据库\n",
    "# 连接数据库\n",
    "dbpath = './数据库/淘宝服装.db'\n",
    "con = sqlite3.connect(dbpath)\n",
    "cursor = con.cursor()\n",
    "\n",
    "# 创建表\n",
    "cursor.execute('''\n",
    "    create table if not exists dim_items (\n",
    "        id integer primary key autoincrement,\n",
    "        item_num varchar,\n",
    "        item_category varchar,\n",
    "        product_num varchar\n",
    "    )''')\n",
    "\n",
    "cursor.execute('''\n",
    "    create table if not exists test_items (\n",
    "        id integer primary key autoincrement,\n",
    "        item_num varchar\n",
    "    )''')\n",
    "con.commit()\n",
    "print(\"表创建成功\")\n",
    "cursor.close()\n",
    "con.close()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f45f3a32",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T07:15:45.131608Z",
     "start_time": "2025-06-17T07:15:45.120608Z"
    }
   },
   "source": [
    "## 查询数据库表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "f31ffb59",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:58:58.008729Z",
     "start_time": "2025-06-17T09:58:57.971727Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "字段是：['id', 'item_num']\n"
     ]
    }
   ],
   "source": [
    "# 连接数据库\n",
    "dbpath = './数据库/淘宝服装.db'\n",
    "con = sqlite3.connect(dbpath)\n",
    "cursor = con.cursor()\n",
    "\n",
    "table_name = 'test_items'\n",
    "cursor.execute(f'pragma table_info(\"{table_name}\")')\n",
    "columns = cursor.fetchall()\n",
    "headers = [column[1] for column in columns]\n",
    "print(f'字段是：{headers}')\n",
    "\n",
    "\n",
    "cursor.close()\n",
    "con.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2ed2bd2d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T11:30:19.250238Z",
     "start_time": "2025-06-17T11:30:13.729922Z"
    }
   },
   "outputs": [],
   "source": [
    "con = sqlite3.connect('./数据库/淘宝服装.db')\n",
    "cur = con.cursor()\n",
    "sql1 = 'select count(*) from test_items as t left join dim_items as d \\\n",
    "        on t.item_num = d.item_num '\n",
    "sql2 = \"select count(*) from dim_items\"\n",
    "cur.execute(sql1)\n",
    "test_item_num = cur.fetchall()[0][0]\n",
    "\n",
    "cur.execute(sql2)\n",
    "dim_item_num = cur.fetchall()[0][0]\n",
    "\n",
    "\n",
    "cur.close()\n",
    "con.close()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e8974535",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T11:29:58.366043Z",
     "start_time": "2025-06-17T11:29:58.339042Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5462"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_item_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f057539b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T11:30:23.622488Z",
     "start_time": "2025-06-17T11:30:23.592486Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "499983"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim_item_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b9eb8622",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T11:29:47.753436Z",
     "start_time": "2025-06-17T11:29:47.732435Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "int"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(dim_item_num)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "063e2d8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect('./数据库/淘宝服装.db')\n",
    "    cur = con.cursor()\n",
    "    sql1 = 'select count(*) from test_items as t left join dim_items as d \\\n",
    "            on t.item_num = d.item_num '\n",
    "    sql2 = \"select count(*) from dim_items group by item_num\"\n",
    "    cur.execute(sql1)\n",
    "    data = cur.fetchall()\n",
    "    test_item_num = data[0][0]\n",
    "    \n",
    "    cur.execute(sql2)\n",
    "    data = cur.fetchall()\n",
    "    dim_item_num = data[0][0]\n",
    "   \n",
    "    cur.close()\n",
    "    con.close()\n",
    "    \n",
    "    # 计算占比\n",
    "    test_proportion = int(round(num2/num1 * 100,0))\n",
    "    other_proportion = 100 - test_proportion"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed1ed435",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T07:25:00.118352Z",
     "start_time": "2025-06-17T07:25:00.106351Z"
    }
   },
   "source": [
    "## 插入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "9c2d9791",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:59:22.664139Z",
     "start_time": "2025-06-17T09:59:12.656567Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据插入成功\n"
     ]
    }
   ],
   "source": [
    "dim_data = dim_list\n",
    "test_data = test_list\n",
    "# 连接数据库\n",
    "dbpath = './数据库/淘宝服装.db'\n",
    "con = sqlite3.connect(dbpath)\n",
    "cursor = con.cursor()\n",
    "\n",
    "cursor.execute('PRAGMA synchronous = OFF')\n",
    "cursor.execute('PRAGMA journal_mode = MEMORY')\n",
    "\n",
    "        \n",
    "\n",
    "try:\n",
    "    con.execute('BEGIN')\n",
    "    cursor.executemany(f\"insert into dim_items\\\n",
    "        (item_num,item_category,product_num)\\\n",
    "        values (?,?,?)\",dim_data)\n",
    "    cursor.executemany(f'insert into test_items\\\n",
    "        (item_num)\\\n",
    "        values (?)',test_data)\n",
    "    con.execute('COMMIT')\n",
    "    print('数据插入成功')\n",
    "except Exception as e:\n",
    "    con.execute(\"ROLLBACK\")\n",
    "    print(f\"插入数据失败：{e}\")\n",
    "        \n",
    "cursor.close()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "d0d2bdc4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T09:59:25.177283Z",
     "start_time": "2025-06-17T09:59:25.132280Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "表字段是：['id', 'item_num', 'item_category', 'product_num']\n",
      "插入的数据是：[(1, '\"29\"', '\"155\"', '\"123950,53517,106068,59598,7503,171811,25618,147905,203432,123580,178091,154365,127004,31897,82406\"'), (2, '\"49\"', '\"228\"', '\"73035,33202,116593,48909,92233,181255,127004,38910,182506,181709,207662,154365,103661,24893\"'), (3, '\"59\"', '\"284\"', '\"123950,38910,22837,5026,15459,47776,158346,101881,131272,176333,196079,23211,148988,144893,167633\"'), (4, '\"109\"', '\"461\"', '\"122071,35420,123950,27207,116593,24893,31897,190554,196564,120213,200685,163272\"'), (5, '\"119\"', '\"368\"', '\"48909,125706,116593,179606,20819,158346,157222,154859,204385,212955,133624,24893\"'), (6, '\"154\"', '\"188\"', '\"48909,53517,116593,55095,15633,100053,141421,121272,62663\"'), (7, '\"179\"', '\"228\"', '\"129343,67965,128624,123950,33202,116593,218823,147905,181255,181709,123491,143862\"'), (8, '\"264\"', '\"368\"', '\"123950,33202,116593,154365,129681,76342,105847,171786,207377,20819,31897,140340,129681,84227\"'), (9, '\"374\"', '\"368\"', '\"90216,136738,38910,129681,64409,107185,151699,142489,20819,157222,130974,94517,80285,105109,109856\"'), (10, '\"414\"', '\"368\"', '\"8640,20864,20819,48909,53517,116593,148988,127004,5026,195998,204385,20819,154365\"')]\n"
     ]
    }
   ],
   "source": [
    "# 连接数据库\n",
    "dbpath = './数据库/淘宝服装.db'\n",
    "con = sqlite3.connect(dbpath)\n",
    "cursor = con.cursor()\n",
    "\n",
    "table_name = 'dim_items'\n",
    "cursor.execute(f'pragma table_info(\"{table_name}\")')\n",
    "columns = cursor.fetchall()\n",
    "headers = [column[1] for column in columns]\n",
    "print(f'表字段是：{headers}')\n",
    "\n",
    "select_sql = f\"select * from {table_name} limit 10\"\n",
    "cursor.execute(select_sql)\n",
    "data = cursor.fetchall()\n",
    "print(f'插入的数据是：{data}')\n",
    "\n",
    "cursor.close()\n",
    "con.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f937f8ba",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T05:55:11.828159Z",
     "start_time": "2025-06-17T05:55:11.812158Z"
    }
   },
   "source": [
    "# 数据可视化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "297d3343",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T10:28:39.993653Z",
     "start_time": "2025-06-17T10:28:34.752353Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mflask\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m Flask,render_template,request\n\u001b[0;32m      2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01msqlite3\u001b[39;00m\n\u001b[0;32m      5\u001b[0m app \u001b[38;5;241m=\u001b[39m Flask(\u001b[38;5;18m__name__\u001b[39m)\n",
      "File \u001b[1;32mD:\\dev\\code\\python\\conda_envs\\env1\\lib\\site-packages\\flask\\__init__.py:6\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mtyping\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mas\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mt\u001b[39;00m\n\u001b[0;32m      5\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m json \u001b[38;5;28;01mas\u001b[39;00m json\n\u001b[1;32m----> 6\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mapp\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m Flask \u001b[38;5;28;01mas\u001b[39;00m Flask\n\u001b[0;32m      7\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mblueprints\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m Blueprint \u001b[38;5;28;01mas\u001b[39;00m Blueprint\n\u001b[0;32m      8\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mconfig\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m Config \u001b[38;5;28;01mas\u001b[39;00m Config\n",
      "File \u001b[1;32mD:\\dev\\code\\python\\conda_envs\\env1\\lib\\site-packages\\flask\\app.py:45\u001b[0m\n\u001b[0;32m     43\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01msansio\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mapp\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m App\n\u001b[0;32m     44\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01msansio\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mscaffold\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m _sentinel\n\u001b[1;32m---> 45\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01msessions\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m SecureCookieSessionInterface\n\u001b[0;32m     46\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01msessions\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m SessionInterface\n\u001b[0;32m     47\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01msignals\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m appcontext_tearing_down\n",
      "File \u001b[1;32mD:\\dev\\code\\python\\conda_envs\\env1\\lib\\site-packages\\flask\\sessions.py:9\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mdatetime\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m datetime\n\u001b[0;32m      7\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mdatetime\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m timezone\n\u001b[1;32m----> 9\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mitsdangerous\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m BadSignature\n\u001b[0;32m     10\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mitsdangerous\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m URLSafeTimedSerializer\n\u001b[0;32m     11\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mwerkzeug\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mdatastructures\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m CallbackDict\n",
      "File \u001b[1;32mD:\\dev\\code\\python\\conda_envs\\env1\\lib\\site-packages\\itsdangerous\\__init__.py:20\u001b[0m\n\u001b[0;32m     18\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mtimed\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m TimedSerializer \u001b[38;5;28;01mas\u001b[39;00m TimedSerializer\n\u001b[0;32m     19\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mtimed\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m TimestampSigner \u001b[38;5;28;01mas\u001b[39;00m TimestampSigner\n\u001b[1;32m---> 20\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01murl_safe\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m URLSafeSerializer \u001b[38;5;28;01mas\u001b[39;00m URLSafeSerializer\n\u001b[0;32m     21\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01murl_safe\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m URLSafeTimedSerializer \u001b[38;5;28;01mas\u001b[39;00m URLSafeTimedSerializer\n\u001b[0;32m     24\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21m__getattr__\u001b[39m(name: \u001b[38;5;28mstr\u001b[39m) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m t\u001b[38;5;241m.\u001b[39mAny:\n",
      "File \u001b[1;32m<frozen importlib._bootstrap>:991\u001b[0m, in \u001b[0;36m_find_and_load\u001b[1;34m(name, import_)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap>:975\u001b[0m, in \u001b[0;36m_find_and_load_unlocked\u001b[1;34m(name, import_)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap>:657\u001b[0m, in \u001b[0;36m_load_unlocked\u001b[1;34m(spec)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap>:562\u001b[0m, in \u001b[0;36mmodule_from_spec\u001b[1;34m(spec)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap>:541\u001b[0m, in \u001b[0;36m_init_module_attrs\u001b[1;34m(spec, module, override)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap>:382\u001b[0m, in \u001b[0;36mcached\u001b[1;34m(self)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap_external>:487\u001b[0m, in \u001b[0;36m_get_cached\u001b[1;34m(filename)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap_external>:422\u001b[0m, in \u001b[0;36mcache_from_source\u001b[1;34m(path, debug_override, optimization)\u001b[0m\n",
      "File \u001b[1;32m<frozen importlib._bootstrap_external>:99\u001b[0m, in \u001b[0;36m_path_join\u001b[1;34m(*path_parts)\u001b[0m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "from flask import Flask,render_template,request\n",
    "import sqlite3\n",
    "\n",
    "\n",
    "app = Flask(__name__)\n",
    "\n",
    "# 表单提交\n",
    "@app.route('/')\n",
    "def index():\n",
    "    return render_template(\"index.html\")\n",
    "\n",
    "@app.route('/index')\n",
    "def home():\n",
    "    return index()\n",
    "\n",
    "@app.route('/movie')\n",
    "def movie():\n",
    "    con = sqlite3.connect('./数据库/淘宝服装.db')\n",
    "    cur = con.cursor()\n",
    "    sql = \"select * from dim_items\"\n",
    "    data = cur.execute(sql)\n",
    "    datalist = [dim_item for dim_item in data]\n",
    "    cur.close()\n",
    "    con.close()\n",
    "    return render_template(\"movie.html\",movies = datalist)\n",
    "\n",
    "@app.route('/score')\n",
    "def score():\n",
    "    con = sqlite3.connect('./数据库/douban_movies.db')\n",
    "    cur = con.cursor()\n",
    "    sql = \"select score,count(score) from movies group by score\"\n",
    "    cur.execute(sql)\n",
    "    data = cur.fetchall()\n",
    "    score = [item[0] for item in data]\n",
    "    num = [item[1] for item in data]\n",
    "    cur.close()\n",
    "    con.close()\n",
    "    \n",
    "    return render_template(\"score.html\",score=score,num=num)\n",
    "\n",
    "@app.route('/word')\n",
    "def word():\n",
    "    return render_template(\"word.html\")\n",
    "\n",
    "@app.route('/team')\n",
    "def team():\n",
    "    return render_template(\"team.html\")\n",
    "\n",
    "    \n",
    "    \n",
    "if __name__ == '__main__':\n",
    "    app.run()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1218183c",
   "metadata": {
    "ExecuteTime": {
     "start_time": "2025-06-17T11:32:57.517Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * Serving Flask app '__main__'\n",
      " * Debug mode: off\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.\n",
      " * Running on http://127.0.0.1:5000\n",
      "Press CTRL+C to quit\n",
      "127.0.0.1 - - [17/Jun/2025 19:33:02] \"GET /dim_item HTTP/1.1\" 404 -\n"
     ]
    }
   ],
   "source": [
    "from flask import Flask,render_template,request\n",
    "import sqlite3\n",
    "from flask_paginate import Pagination,get_page_args\n",
    "\n",
    "\n",
    "app = Flask(__name__)\n",
    "\n",
    "# 表单提交\n",
    "@app.route('/')\n",
    "def index():\n",
    "    return render_template(\"index.html\")\n",
    "\n",
    "@app.route('/index')\n",
    "def home():\n",
    "    return index()\n",
    "\n",
    "# 存储每页展示的数据条数\n",
    "app.config['PER_PAGE'] = 20\n",
    "\n",
    "\n",
    "@app.route('/movie', methods=['POST','GET'])\n",
    "def dim_item():\n",
    "    # 获取分页数据，   默认值为  page = 1  per_page = 10\n",
    "    page,per_page,offset=get_page_args(get_page_parameter='page',get_per_page_parameter='per_page')\n",
    "    \n",
    "    per_page = app.config['PER_PAGE']\n",
    "    offset = (page - 1) * per_page\n",
    "    \n",
    "    if request.method == 'POST':\n",
    "        temp = request.form['per_page']\n",
    "        # 更新数据\n",
    "        app.config['PER_PAGE'] = int(temp)\n",
    "        per_page = int(temp)\n",
    "        page = 1\n",
    "        \n",
    "    # 连接数据库\n",
    "    con = sqlite3.connect(\"./数据库/淘宝服装.db\")\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    pages_sql = f'select * from dim_items limit {per_page } offset {offset }'\n",
    "    total_sql = f'select count(*) from dim_items' \n",
    "    print(pages_sql)\n",
    "    \n",
    "    data = cur.execute(pages_sql)\n",
    "    dim_item_lists = [dim_item for dim_item in data]\n",
    "    \n",
    "    cur.execute(total_sql)\n",
    "    total = cur.fetchall()[0][0]\n",
    "        \n",
    "    \n",
    "    cur.close()\n",
    "    con.close()\n",
    "    pagination = Pagination(page=page,per_page=per_page,total=total)\n",
    "    \n",
    "    return render_template('movie.html',dim_items = dim_item_lists,\n",
    "                           pagination = pagination,per_page = per_page,page=page)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "@app.route('/score')\n",
    "def score():\n",
    "    con = sqlite3.connect('./数据库/淘宝服装.db')\n",
    "    cur = con.cursor()\n",
    "    sql1 = 'select count(*) from test_items as t left join dim_items as d \\\n",
    "            on t.item_num = d.item_num '\n",
    "    sql2 = \"select count(*) from dim_items\"\n",
    "    cur.execute(sql1)\n",
    "    test_item_num = cur.fetchall()[0][0]\n",
    "\n",
    "    cur.execute(sql2)\n",
    "    dim_item_num = cur.fetchall()[0][0]\n",
    "\n",
    "\n",
    "    cur.close()\n",
    "    con.close()\n",
    "\n",
    "    # 计算占比\n",
    "    test_proportion = int(round(test_item_num/dim_item_num * 100,0))\n",
    "    other_proportion = 100 - test_proportion\n",
    "    return render_template(\"score.html\",test_proportion=test_proportion,other_proportion=other_proportion)\n",
    "\n",
    "@app.route('/word')\n",
    "def word():\n",
    "    return render_template(\"word.html\")\n",
    "\n",
    "@app.route('/team')\n",
    "def team():\n",
    "    return render_template(\"team.html\")\n",
    "\n",
    "    \n",
    "    \n",
    "if __name__ == '__main__':\n",
    "    app.run()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "45fe3214",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T11:10:10.964128Z",
     "start_time": "2025-06-17T11:10:10.941127Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "48"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int(round(32/67 * 100,0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d64b107",
   "metadata": {},
   "outputs": [],
   "source": [
    "from flask import Flask,render_template,request\n",
    "import sqlite3\n",
    "from flask_paginate import Pagination,get_page_args\n",
    "\n",
    "\n",
    "app = Flask(__name__)\n",
    "\n",
    "# 表单提交\n",
    "@app.route('/')\n",
    "def index():\n",
    "    return render_template(\"index.html\")\n",
    "\n",
    "@app.route('/index')\n",
    "def home():\n",
    "    return index()\n",
    "\n",
    "# 存储每页展示的数据条数\n",
    "app.config['PER_PAGE'] = 20\n",
    "\n",
    "\n",
    "@app.route('/movie', methods=['POST','GET'])\n",
    "def dim_item():\n",
    "    # 获取分页数据，   默认值为  page = 1  per_page = 10\n",
    "    page,per_page,offset=get_page_args(get_page_parameter='page',get_per_page_parameter='per_page')\n",
    "    \n",
    "    per_page = app.config['PER_PAGE']\n",
    "    offset = (page - 1) * per_page\n",
    "    \n",
    "    if request.method == 'POST':\n",
    "        temp = request.form['per_page']\n",
    "        # 更新数据\n",
    "        app.config['PER_PAGE'] = int(temp)\n",
    "        per_page = int(temp)\n",
    "        page = 1\n",
    "        \n",
    "    # 连接数据库\n",
    "    con = sqlite3.connect(\"./数据库/淘宝服装.db\")\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    pages_sql = f'select * from dim_items limit {per_page } offset {offset }'\n",
    "    total_sql = f'select count(*) from dim_items' \n",
    "    print(pages_sql)\n",
    "    \n",
    "    data = cur.execute(pages_sql)\n",
    "    dim_item_lists = [dim_item for dim_item in data]\n",
    "    \n",
    "    cur.execute(total_sql)\n",
    "    total = cur.fetchall()[0][0]\n",
    "        \n",
    "    \n",
    "    cur.close()\n",
    "    con.close()\n",
    "    pagination = Pagination(page=page,per_page=per_page,total=total)\n",
    "    \n",
    "    return render_template('movie.html',dim_items = dim_item_lists,\n",
    "                           pagination = pagination,per_page = per_page,page=page)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "@app.route('/item')\n",
    "def item():\n",
    "    con = sqlite3.connect('./数据库/淘宝服装.db')\n",
    "    cur = con.cursor()\n",
    "    sql1 = \"select item_num,count(item_num) from dim_items group by item_num\"\n",
    "    sql2 = \"select item_num,count(item_num) from test_items group by item_num\"\n",
    "    cur.execute(sql1)\n",
    "    data = cur.fetchall()\n",
    "    score = [item[0] for item in data]\n",
    "    num = [item[1] for item in data]\n",
    "    \n",
    "    cur.execute(sql2)\n",
    "    data = cur.fetchall()\n",
    "    score = [item[0] for item in data]\n",
    "    num = [item[1] for item in data]\n",
    "   \n",
    "    cur.close()\n",
    "    con.close()\n",
    "    \n",
    "    return render_template(\"score.html\",score=score,num=num)\n",
    "\n",
    "@app.route('/word')\n",
    "def word():\n",
    "    return render_template(\"word.html\")\n",
    "\n",
    "@app.route('/team')\n",
    "def team():\n",
    "    return render_template(\"team.html\")\n",
    "\n",
    "    \n",
    "    \n",
    "if __name__ == '__main__':\n",
    "    app.run()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "01ccf106",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-17T11:07:57.136473Z",
     "start_time": "2025-06-17T11:07:57.105472Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = 20/45 * 100\n",
    "int(round(a,0))\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env1",
   "language": "python",
   "name": "env1"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.20"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "204.797px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
